from typing import Union
import os
from dotenv import load_dotenv
from psycopg_pool import AsyncConnectionPool
from contextlib import asynccontextmanager

from fastapi import FastAPI,Request

load_dotenv()
    

@asynccontextmanager
async def lifespan(app: FastAPI):
    psql_url = os.environ.get('PSQL_URI')
    app.async_pool = AsyncConnectionPool(conninfo=psql_url)
    yield
    await app.async_pool.close()

app = FastAPI(lifespan=lifespan)


# @app.get("/")
# def read_root():
#     return {"Hello": "World"}


#   ORDER BY RANDOM()

@app.get("/encourage/preview/v1")
async def encourage_preview_v1(request: Request):
    async with request.app.async_pool.connection() as conn:
        async with conn.cursor() as cur:
            await cur.execute("""
                              SELECT text FROM text_v1 
                              WHERE count = (SELECT MIN(count) FROM text_v1) AND version = 'encourage_v1'
                              ORDER BY RANDOM()
                              LIMIT 1;
                              """)
            results = await cur.fetchone()
            return {"text": results[0]}
        
        
        
@app.get("/encourage/cost/v1")
async def encourage_cost_v1(request: Request):
    async with request.app.async_pool.connection() as conn:
        async with conn.cursor() as cur:
            await cur.execute("""
                              SELECT id, text FROM text_v1 
                              WHERE count = (SELECT MIN(count) FROM text_v1) AND version = 'encourage_v1'
                              ORDER BY created_at
                              LIMIT 1;
                              """)
            results = await cur.fetchone()

            if results:
                # 更新这条记录
                await cur.execute("""
                    UPDATE text_v1 SET count = count + 1 WHERE id = %s;
                """, (results[0],))

            # 提交事务
            await conn.commit()
    
            return {"text": results[1]}



@app.get("/story/preview/v1")
async def story_preview_v1(request: Request):
    async with request.app.async_pool.connection() as conn:
        async with conn.cursor() as cur:
            await cur.execute("""
                              SELECT text FROM text_v1 
                              WHERE count = (SELECT MIN(count) FROM text_v1 WHERE version LIKE 'story_%') AND version LIKE 'story_%'
                              ORDER BY RANDOM()
                              LIMIT 1;
                              """)
            results = await cur.fetchone()
            return {"text": results[0]}
        
        
        
@app.get("/story/cost/v1")
async def story_cost_v1(request: Request):
    async with request.app.async_pool.connection() as conn:
        async with conn.cursor() as cur:
            await cur.execute("""
                              SELECT id, text FROM text_v1 
                              WHERE count = (SELECT MIN(count) FROM text_v1 WHERE version LIKE 'story_%') AND version LIKE 'story_%'
                              ORDER BY LENGTH(text) ASC
                              LIMIT 1;
                              """)
            results = await cur.fetchone()

            if results:
                # 更新这条记录
                await cur.execute("""
                    UPDATE text_v1 SET count = count + 1 WHERE id = %s;
                """, (results[0],))

            # 提交事务
            await conn.commit()
    
            return {"text": results[1]}

